﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.Common;
using System.Data.OleDb;
using QLDT.QLDTDatasetTableAdapters;
using Microsoft.Office.Interop.Excel;
using DTO;
using BUS;

namespace QLDT
{
    public partial class NhapDSSVBangExecl : DevExpress.XtraEditors.XtraForm
    {
        public NhapDSSVBangExecl()
        {
            InitializeComponent();
        }

        private void simpleBt_browse_Click(object sender, EventArgs e)
        {
            //openFileDialogExel.Filter = "Excel Files |*.xls|Excel Files 2007 |*.xlsx";
            openFileDialog.FileName = "";
            openFileDialog.ShowDialog();
        }

        private void openFileDialog_FileOk(object sender, CancelEventArgs e)
        {
            String urlFile = openFileDialog.FileName;
            txt_path.Text = urlFile;
            List<String> sheets = getListSheet(urlFile);
            cbm_sheet.DataSource = sheets;
        }
        private List<String> getListSheet(String urlFile)
        {
            try
            {
                List<String> sheets = new List<String>();
                String connec = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", urlFile);
                DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                DbConnection connection = factory.CreateConnection();
                connection.ConnectionString = connec;
                connection.Open();
                System.Data.DataTable dt = connection.GetSchema("Tables");
                connection.Close();
                foreach (DataRow row in dt.Rows)
                {
                    String sheetnames = (String)row["TABLE_NAME"];
                    sheets.Add(sheetnames);
                }
                return sheets;
            }
            catch (Exception)
            {
                return null;
            }
        }

        private void cbm_sheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                String connec = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", txt_path.Text);
                String query = String.Format("Select * from [{0}]", cbm_sheet.Text);
                OleDbDataAdapter adapter = new OleDbDataAdapter(query, connec);
                System.Data.DataTable tbSinhVien = new System.Data.DataTable();
                adapter.Fill(tbSinhVien);
                if (tbSinhVien.Rows.Count > 0)
                {
                    dataGridViewSinhVien.DataSource = tbSinhVien;
                }
                else
                {
                    dataGridViewSinhVien.DataSource = null;
                }
            }
            catch (Exception)
            {
                //MessageBox.Show("Chưa chọn File hoặc đường dẫn File sai");
            }
        }

        private void simpleBt_thucthi_Click(object sender, EventArgs e)
        {
            SINHVIENTableAdapter adapter = new SINHVIENTableAdapter();
            int count = 0;
       
               foreach (DataGridViewRow row in dataGridViewSinhVien.Rows)
               {
                   try
                   {
                       DTO_SinhVien SV = new DTO_SinhVien();
                       SV.MaSV = row.Cells["MaSV"].Value.ToString();
                       SV.TenSV = row.Cells["TenSV"].Value.ToString();

                       if (row.Cells["GioiTinh"].Value.ToString().Equals("1"))
                       {
                           SV.GioiTinh = true;
                       }
                       else
                       {
                           SV.GioiTinh = false;
                       }

                       SV.NgaySinh = DateTime.Parse(row.Cells["NgaySinh"].Value.ToString());
                       SV.MaLop = row.Cells["MaLop"].Value.ToString();
                       SV.DiaChi = row.Cells["DiaChi"].Value.ToString();
                       SV.Email = row.Cells["Email"].Value.ToString();
                       SV.SDT = row.Cells["SDT"].Value.ToString();
                       //adapter.InsertQuery(MaSV, TenSV, GioiTinh, NgaySinh, MaLop, DiaChi, Email, SDT);
                       BUS_SinhVien.insertSinhVien(SV);
                       count++;
                   }
                   catch
                   {
                       continue;
                   }
           }
           MessageBox.Show("Đã thêm thành công"+count+"Và thất bại"+(dataGridViewSinhVien.RowCount-count)+"");
        }
        public void LoadLop()
        {
            List<DTO_Lop> bp = new List<DTO_Lop>();
            bp = BUS_Lop.SelectLop();
            if (cmb_lop.Items.Count > 0)
                cmb_lop.Items.Clear();
            cmb_lop.Items.Add("---Chọn tên lớp cần thực thi---");
            for (int i = 0; i < bp.Count; i++)
            {
                cmb_lop.Items.Add(bp[i].Malop);
                cmb_lop.ValueMember = bp[i].Malop;
                cmb_lop.DisplayMember = bp[i].Malop;
            }
            cmb_lop.SelectedItem = "---Chọn tên lớp cần thực thi---";
        }
        public void LoadKhoaHoc()
        {
            List<DTO_KhoaHoc> bp = new List<DTO_KhoaHoc>();
            bp = BUS_KhoaHoc.SelectKhoaHoc();
            if (cmb_lop.Items.Count > 0)
                cmb_lop.Items.Clear();
            cmb_lop.Items.Add("---Chọn tên khóa học cần thực thi---");
            for (int i = 0; i < bp.Count; i++)
            {
                cmb_lop.Items.Add(bp[i].MaKH);
                cmb_lop.ValueMember = bp[i].MaKH;
                cmb_lop.DisplayMember = bp[i].MaKH;
            }
            cmb_lop.SelectedItem = "---Chọn tên  khóa học cần thực thi---";
        }
        public void selectSinhvienExcle(int luachon, string k)
        {
            Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
            Workbook xlWorkBook = xla.Workbooks.Add(XlSheetType.xlWorksheet);
            Worksheet xlWorkSheet = (Worksheet)xla.ActiveSheet;
            xla.Visible = true;
            string malop = cmb_lop.Text;
            string maKhoaHoc = cmb_lop.Text;
            List<DTO_SinhVien> nv = new List<DTO_SinhVien>();
            nv = BUS_SinhVien.selectSinhVienfull(luachon, k, malop, maKhoaHoc);

            xlWorkSheet.Cells[1, 1] = "MaSV";
            xlWorkSheet.Cells[1, 2] = "TenSV";
            xlWorkSheet.Cells[1, 3] = "GioiTinh";
            xlWorkSheet.Cells[1, 4] = "NgaySinh";
            xlWorkSheet.Cells[1, 5] = "MaLop";
            xlWorkSheet.Cells[1, 6] = "DiaChi";
            xlWorkSheet.Cells[1, 7] = "Email";
            xlWorkSheet.Cells[1, 8] = "SDT";


            for (int i = 0; i < nv.Count; i++)
            {
                int j = 1;
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].MaSV.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].TenSV.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].GioiTinh.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].NgaySinh.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].MaLop.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].DiaChi.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].Email.ToString();
                xlWorkSheet.Cells[i + 2, j++] = "'" + nv[i].SDT.ToString();
            }

        }

        private void simpleBt_Xuat_Click(object sender, EventArgs e)
        {
            if (cmb_luachon.SelectedItem == "Xuất toàn bộ thông tin sinh viên")
            {
                selectSinhvienExcle(0, "null");
            }
            else if (cmb_luachon.SelectedItem == "Chỉ xuất theo lớp")
            {
                if (cmb_lop.Text != "---Chọn tên lớp cần thực thi---")
                    selectSinhvienExcle(1, cmb_lop.Text);
                else
                    MessageBox.Show("Bạn chưa chọn lớp để thực thi", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                if (cmb_lop.Text != "---Chọn khóa học thực thi---")
                    selectSinhvienExcle(2, cmb_lop.Text);
                else
                    MessageBox.Show("Bạn chưa chọn khóa học để thực thi", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void cmb_luachon_SelectedValueChanged(object sender, EventArgs e)
        {
            if (cmb_luachon.SelectedItem == "Xuất toàn bộ thông tin thông tin sinh vien")
            {
                layoutControlItem11.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.OnlyInCustomization;
                
            
            }
            else if (cmb_luachon.SelectedItem == "Chỉ xuất theo lớp")
            {
                layoutControlItem11.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
                
                LoadLop();
            }
            else
            {
                layoutControlItem11.Visibility = DevExpress.XtraLayout.Utils.LayoutVisibility.Always;
               
                LoadKhoaHoc();
            }
        }

    }
}